import requests
from bs4 import BeautifulSoup
import pymysql
print('connect to mysql ...')
db = pymysql.connect(host='localhost', user='root', password='1x@zs20$', db='test')
print('connected mysql!')
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS COLOR")

sql = """CREATE TABLE COLOR(
        Color CHAR(20) NOT NULL,
        Value CHAR(10),
        Style CHAR(50) )"""
cursor.execute(sql)
hdrs = {'User-Agent':'Mozilla/5.0 (X11; Fedora; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko)'}
url = "http://html-color-codes.info/color-names/"
r = requests.get(url, headers=hdrs)
soup = BeautifulSoup(r.content.decode('gbk', 'ignore',), 'lxml')
trs = soup.find_all('tr')
for tr in trs:
    style = tr.get('style')
    tds = tr.find_all('td')
    td = [x for x in tds]
    name = td[1].text.strip()
    hex = td[2].text.strip()
    insert_color = "INSERT INTO COLOR(Color,Value,Style)" "VALUES(%s,%s,%s)"
    data_color = (name, hex, style)
    cursor.execute(insert_color, data_color)
    db.commit()
print('finish...')
